package com.sun.myproject.servlet;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.sun.myproject.bean.Book;
import com.sun.myproject.bean.PageBean;
import com.sun.myproject.dao.BookDao;
import com.sun.myproject.daoimpl.BookDaoImpl;

/**
 * Servlet implementation class ExportExcelServlet
 */
@WebServlet("/ExportExcelServlet")
public class ExportExcelServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	public ExportExcelServlet() {
		super();
		// TODO Auto-generated constructor stub
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		try {
			request.setCharacterEncoding("utf-8");
			response.setContentType("text/html;charset=utf-8");
			// 1. 获取需要显示的页码数

			String bookName = request.getParameter("bookname");

			System.out.println("----------------$$$$------------------" + bookName);

			BookDao service = new BookDaoImpl();
			List<Book> list = service.exportExcel(bookName);
			System.out.println(list.toString());

			Workbook wordkbook = ExcelData(list);// 创建excel
			/*
			 * File file = new File(path);
			 * 
			 * response.addHeader("Content-Type","application/octet-stream");//设置生成的文件类型  
			 * response.setContentType("application/msexcel");
			 * response.addHeader("Content-Disposition", "attachment;filename=" +
			 * URLEncoder.encode(file.getName(), "utf-8")); InputStream in = new
			 * FileInputStream(file); OutputStream out = response.getOutputStream(); byte[]
			 * buffer = new byte[1024]; int len; while ((len = in.read(buffer)) != 1) {
			 * out.write(buffer, 0, len); }
			 */
			
			try {
				Date day=new Date();
			    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			    String name = "图书数据"+ df.format(day);
			    String codedFileName = new String(name.getBytes("gbk"), "iso-8859-1");
			    response.setHeader("Content-Disposition", "attachment;filename=" + codedFileName + ".xls");
			    // 响应类型,编码
			    response.setContentType("application/octet-stream;charset=UTF-8");
			    // 形成输出流
			    OutputStream osOut = response.getOutputStream();
			    // 将指定的字节写入此输出流
			    wordkbook.write(osOut);
			    // 刷新此输出流并强制将所有缓冲的输出字节被写出
			    osOut.flush();
			    // 关闭流
			    osOut.close();
			   
			   
			} catch (Exception e) {
				e.printStackTrace();
			}
			// 3. 跳转界面。

			request.getRequestDispatcher("BookListServlet").forward(request, response);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

	public Workbook ExcelData(List<Book> list) throws Exception {
		System.out.println("****************************************");
		// 时间
		long begin = System.currentTimeMillis();
		/*
		 * String path = getServletContext().getRealPath(""); System.out.println(path);
		 * File file = new File(path); if (!file.exists()) { file.mkdir(); }
		 */
		// 1，创建一个工作薄
		Workbook wordkbook = new HSSFWorkbook();
		// 表名
		Sheet sheet = wordkbook.createSheet("book");
		
		
		// 写入数据
		  Row row1=sheet.createRow(0);
	        //4.创建一个单元格
	        Cell cell6 = row1.createCell(0);
	        cell6.setCellValue("书名");
	        Cell cell7 = row1.createCell(1);
	        cell7.setCellValue("出版社");
	        Cell cell8 = row1.createCell(2);
	        cell8.setCellValue("作者");
	        Cell cell9 = row1.createCell(3);
	        cell9.setCellValue("本数");
	        Cell cell10 = row1.createCell(4);
	        cell10.setCellValue("类型");
	        Cell cell11 = row1.createCell(5);
	        cell11.setCellValue("图片");
		
		int rowNum = 1;
		for (Book book : list) {
			System.out.println(list.size());
		
			System.out.println("---------------______"+rowNum);
			System.out.println(book.toString());
			Row row = sheet.createRow(rowNum++);
			

				for (int cellNum = 0; cellNum < 6; cellNum++) {
					System.out.println(cellNum);
						
					switch (cellNum) {
					case 0:
						Cell cell = row.createCell(cellNum);
						System.out.println(book.getBookname());
						cell.setCellValue(book.getBookname());// 设值
						break;
					case 1:
						Cell cell1 = row.createCell(cellNum);
						cell1.setCellValue(book.getPublish());// 设值
						break;
					case 2:
						Cell cell2 = row.createCell(cellNum);
						cell2.setCellValue(book.getAuthor());// 设值
						break;
					case 3:
						Cell cell3 = row.createCell(cellNum);
						cell3.setCellValue(book.getBookcount());// 设值
						break;
					case 4:
						Cell cell4 = row.createCell(cellNum);
						cell4.setCellValue(book.getBooktype());// 设值
						break;
					case 5:
						Cell cell5 = row.createCell(cellNum);
						cell5.setCellValue(book.getImg());// 设值
						break;
					
					}
					
				}
				
				
				
			
		}
		
		
	


		System.out.println("over");
		//FileOutputStream fileOutputStream = new FileOutputStream(path + "\\excel.xls");
	//	wordkbook.write(fileOutputStream);

		//fileOutputStream.close();
		//System.out.println("灰灰统计表03已生成");
		//long end = System.currentTimeMillis();
		//System.out.println((double) (end - begin) / 1000);
		return wordkbook;
		
	}
}
